package com.ycdx.impms.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.log4j.Logger;

import com.ycdx.impms.pojo.User;
import com.ycdx.impms.utils.DBPool;
import com.ycdx.impms.utils.MD5Util;

public class UserImpl {	

	static Logger logger = Logger.getLogger(UserImpl.class.getName());
	
	/**
	 * 根据帐号密码判断用户是否合法
	 * @param username
	 * @param password
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static boolean isLeagal(String username,String password){
		boolean result = false;
		
		String sql = "select * from tb_user where username =? and password =?;";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {username,password};
		try {
			User tempUser = (User) qr.query(conn, sql, new BeanHandler(User.class),params);
			
			if(tempUser!=null){
				//合法用户
				result = true;
			}
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-isLeagal()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-isLeagal()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;		
	}
	
	
	/**
	 * 根据帐号密码获取用户
	 * @param username
	 * @param password
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static User getUser(String username,String password){
		User retUser = null;
		String sql = "select * from tb_user where username=? and password=?";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {username,password};		
		try {
			retUser = (User) qr.query(conn, sql, new BeanHandler(User.class), params);			
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-getUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-getUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return retUser;		
	}
	
	@SuppressWarnings("unchecked")
	public static User getUserById(String id){
		User retUser = null;
		String sql = "select * from tb_user where id=?";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		Object[] params = {id};		
		try {
			retUser = (User) qr.query(conn, sql, new BeanHandler(User.class), params);			
		}  catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-getUserById()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-getUserById()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return retUser;		
	}
	
	
	/**
	 * 更新指定id的用户信息
	 * @param id
	 * @param ryxx
	 * @return
	 */
	public static int updateUserPass(String id,String newPass){
		String sql = "update tb_user set " +
				"password=?" +
				"where id=?;";
		Object[] params = {MD5Util.MD5(newPass),id};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-updateUserPass()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-updateUserPass()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	@SuppressWarnings("unchecked")
	public static List<User> getUserList(int start,int number) {
		ArrayList<User> result = new ArrayList<User>();
		String sql = "select * from tb_user limit ?,?";
		//System.out.println(sql);
		Object[] params = {start,number};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		try {
			result = (ArrayList<User>) qr.query(conn, sql, new BeanListHandler(User.class),params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("UserImpl.class-getUserList()-数据库操作失败！");
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-getUserList()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 查询记录总数
	 * @param area
	 * @return
	 */
	public static int getCount(){
		Map<String,Object> totalMap = new HashMap<String, Object>();
		int total ;
		String sql = "select count(*) total from tb_user;";
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		try {
			totalMap = (HashMap<String, Object>)qr.query(conn,sql, new MapHandler());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.error("UserImpl.class-getCount()-数据库操作失败！");
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-getCount()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		//total = Integer.parseInt((String)totalMap.get("total"));
		total = Integer.parseInt( totalMap.get("total").toString());
		//System.out.println(total);
		return total;
	}
	
	/**
	 * 添加用户信息
	 * @param user
	 * @return
	 */
	public static int addUser(User user){
		String sql = "insert into tb_user (" +
				"username," +
				"password," +
				"name," +
				"dw," +
				"department," +
				"role"+
				") values(?,?,?,?,?,?);";
		Object[] params = {user.getUsername(),MD5Util.MD5("123456"),user.getName()
				,user.getDw(),user.getDepartment(),user.getRole()};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-addUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-addUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	/**
	 * 更新指定id的用户信息
	 * @param id
	 * @param ryxx
	 * @return
	 */
	public static int updateUser(int id,User user){
		String sql = "update tb_user set " +
				"username=?," +
				"name=?," +
				"dw=?," +
				"department=?," +
				"role=?" +
				"where id=?;";
		Object[] params = {user.getUsername(),user.getName(),user.getDw(),user.getDepartment(),user.getRole(),id};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-updateUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-updateUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 根据id删除用户信息
	 * @param id
	 * @return
	 */
	public static int removeUser(int id){
		String sql = "delete from tb_user where id=?";
		Object[] params = {id};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-removeUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-removeUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	/**
	 * 根据id重置用户密码
	 * @param id
	 * @return
	 */
	public static int resetUser(int id){
		String sql = "update tb_user set password = ? where id=?";
		Object[] params = {MD5Util.MD5("123456"),id};
		Connection conn = DBPool.getInstance().getConnection();
		QueryRunner qr = new QueryRunner();
		
		int flag = 0;
		try {
			flag = qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.error("UserImpl.class-resetUser()-数据库操作失败！");
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				logger.error("UserImpl.class-resetUser()-连接关闭失败！");
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
}
